﻿using System;
using System.Configuration;
using System.Collections;
using System.Data;
using System.Data.Sql;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using MySql.Data.MySqlClient;

/// <summary>
/// 数据库通用操作方法封装
/// </summary>
public class DBO
{
    private MySqlConnection conn = null;
    private MySqlCommand cmd = null;

    public DBO()
    {
        //TODO: 在此处添加构造函数逻辑
    }


    #region 创建/释放数据库连接
    /// <summary>
    /// 打开数据库连接
    /// </summary>
    public void OpenConn()
    {
        conn = new MySqlConnection();
        conn.ConnectionString = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["DBConnString"]);

        if (conn.State == System.Data.ConnectionState.Closed)
        {
            conn.Open();
        }
    }

    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    public void CloseConn()
    {
        if (conn != null)
            if (conn.State.ToString().ToLower() == "open")
            {
                conn.Close();
                conn.Dispose();
            }
    }

    /// <summary>
    /// 重设数据库连接
    /// </summary>
    public void ResetConn()
    {
        CloseConn();
        conn = null;
        cmd = null;
    }

    /// <summary>
    /// 获得一个数据库连接
    /// </summary>
    /// <returns>数据库连接对象</returns>
    public MySqlConnection GetConn()
    {
        OpenConn();
        return conn;
    }
    #endregion


    #region 将参数格式化为MySqlParameter类型
    /// <summary>
    /// 将参数格式化为MySqlParameter类型
    /// </summary>
    /// <param name="sParameterName"></param>
    /// <param name="sdtType">参数的数据类型</param>
    /// <param name="iSize">参数大小，获取或设置列中数据的最大大小（以字节为单位）</param>
    /// <param name="pdDirection">参数方向，指示参数是只可输入、只可输出、双向还是存储过程返回值参数</param>
    /// <param name="oValue">需要存储的参数值</param>
    /// <returns>格式化后的SqlParameter对象</returns>
    public MySqlParameter CreateParameter(string sParameterName, MySqlDbType sdtType, Int32 iSize,ParameterDirection pdDirection,object oValue)
    {
        try
        {
            MySqlParameter myParam;
            if (iSize > 0)
            {
                myParam = new MySqlParameter(sParameterName, sdtType, iSize);
            }
            else
            {
                myParam = new MySqlParameter(sParameterName, sdtType);
            }
            myParam.Direction = pdDirection;
            if (!(pdDirection == ParameterDirection.Output && oValue == null))
            {
                myParam.Value = oValue;
            }
            return myParam;
        }
        catch (MySqlException ex)
        {
            throw (ex);
        }
    }
    #endregion  
    

    #region 创建一个MySqlCommand对象以执行命令文本
    /// <summary>
    /// 创建一个SqlCommand对象以执行命令文本
    /// </summary>
    /// <param name="sSQL">文本命令</param>
    /// <param name="spPramas">填充的参数</param>
    /// <returns>SqlCommand对象</returns>
    public MySqlCommand CreatCommand(string sSQL, MySqlParameter[] spPramas)
    {
        try
        {
            this.OpenConn();
            cmd = new MySqlCommand(sSQL, conn);
            cmd.CommandType = CommandType.Text;

            //依次把参数加入命令文本
            if (spPramas != null)
            {
                foreach (MySqlParameter p in spPramas)
                {
                    cmd.Parameters.Add(p);
                }
            }

            //加入执行返回结果参数
            cmd.Parameters.Add(new MySqlParameter("RetureValue", MySqlDbType.Int32, 4,
                ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));

            return cmd;
        }
        catch (MySqlException ex)
        {
            throw (ex);
        }
    }
    #endregion


    #region 参数化执行一个SQL命令，并返回影响的行数
    /// <summary>
    /// 参数化执行一个SQL命令，并放回影响的行数
    /// </summary>
    /// <param name="sSQL">文本命令</param>
    /// <param name="spPramas">参数</param>
    /// <returns>影响的行数</returns>
    public int ExecProcBySQLandParams(string sSQL, MySqlParameter[] spPramas)
    {
        try
        {
            MySqlCommand cmd = CreatCommand(sSQL, spPramas);
            cmd.ExecuteNonQuery();
            this.CloseConn();

            return (int)cmd.Parameters["ReturnValue"].Value;
        }
        catch (MySqlException ex)
        {
            throw (ex);
        }
    }
    #endregion


    #region 参数化执行一个MySQL存储过程，并放回影响的行数
    /// <summary>
    /// 参数化执行一个SQL命令，并放回影响的行数
    /// </summary>
    /// <param name="sProcName">存储过程名称</param>
    /// <param name="spPramas">参数</param>
    /// <returns>影响的行数</returns>
    public int ExecProcDirectly(string sProcName, MySqlParameter[] spPramas)
    {
        try
        {
            this.OpenConn();
            MySqlCommand cmd = new MySqlCommand(sProcName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            foreach(MySqlParameter p in spPramas)
            {
                cmd.Parameters.Add(p);
            }
            cmd.ExecuteNonQuery();
            this.CloseConn();

            //return (int)cmd.Parameters["ReturnValue"].Value;
            return 1;
        }
        catch (MySqlException ex)
        {
            throw (ex);
        }
    }
    #endregion


    #region 直接执行SQL语句
    /// <summary>
    /// 直接执行SQL语句
    /// </summary>
    /// <param name="sSQL">需执行的SQL语句</param>
    /// <returns>执行结果</returns>
    public bool ExecSQLDirectly(string sSQL)
    {
        try
        {
            this.OpenConn();
            MySqlCommand cmd = new MySqlCommand(sSQL, conn);
            cmd.ExecuteNonQuery();
            this.CloseConn();
            return true;
        }
        catch (MySqlException ex)
        {
            throw (ex);
            return false;
        }
    }
    #endregion


    #region 参数化执行查询命令文本，并且返回DataSet数据集
    /// <summary>
    /// 参数化执行查询命令文本，并且返回DataSet数据集
    /// </summary>
    /// <param name="procName">命令文本</param>
    /// <param name="prams">参数对象</param>
    /// <param name="tbName">数据表名称</param>
    /// <returns>DataSet对象</returns>
    public DataSet RunProcReturn(string sSQL, MySqlParameter[] prams, string tbName)
    {
        try
        {
            MySqlDataAdapter dap = CreateDataAdaper(sSQL, prams);
            DataSet ds = new DataSet();
            dap.Fill(ds, tbName);
            this.CloseConn();
            //得到执行成功返回值
            return ds;
        }
        catch (MySqlException ex)
        {
            throw (ex);
        }
    }
    #endregion


    #region 直接执行SQL语句，并且返回DataSet数据集
    /// <summary>
    /// 直接执行SQL语句，并且返回DataSet数据集
    /// </summary>
    /// <param name="procName">命令文本</param>
    /// <param name="tbName">数据表名称</param>
    /// <returns>数据集</returns>
    public DataSet GetDataSet(string sSQL, string sTBName)
    {
        try
        {
            MySqlDataAdapter sda = CreateDataAdaper(sSQL, null);
            DataSet ds = new DataSet();
            sda.Fill(ds, sTBName);
            this.CloseConn();

            //得到执行成功返回值
            return ds;
        }
        catch (MySqlException ex)
        {
            throw (ex);
        }
    }
    #endregion


    #region 创建一个SqlDataAdapter对象
    /// <summary>
    /// 创建一个SqlDataAdapter对象
    /// </summary>
    /// <param name="procName">命令文本</param>
    /// <param name="prams">参数对象</param>
    /// <returns>SqlDataAdapter对象</returns>
    private MySqlDataAdapter CreateDataAdaper(string sSQL, MySqlParameter[] spParams)
    {
        try
        {
            this.OpenConn();
            MySqlDataAdapter sda = new MySqlDataAdapter(sSQL, conn);
            sda.SelectCommand.CommandType = CommandType.Text;  //执行类型：命令文本
            if (spParams != null)
            {
                foreach (MySqlParameter p in spParams)
                    sda.SelectCommand.Parameters.Add(p);
            }

            //加入返回参数
            sda.SelectCommand.Parameters.Add(new MySqlParameter("ReturnValue", MySqlDbType.Int32, 4,
                ParameterDirection.ReturnValue, false, 0, 0,
                string.Empty, DataRowVersion.Default, null));

            return sda;
        }
        catch (MySqlException ex)
        {
            throw (ex);
        }
    }
    #endregion
}